﻿<%@ WebHandler Language="C#" Class="UploadDataHandler" %>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;

using Training.Library.DataAccess.Administration;
using Training.Library.DomainObject.Administration;
using Training.Library.DataAccess.Administration.Catalog;
using Training.Library.DomainObject.Administration.Catalog;

using SIPO.Library.DataAccess.Administration.Catalog;
using SIPO.Library.DomainObject.Administration.Catalog;

using Training.Library.Enum;
using Assesment.Web.Support;
using Newtonsoft.Json.Linq;
using Excel;
using System.IO;
using System.Data;

public class UploadDataHandler : Assesment.Web.BaseHandler
{

    private recLogRepo logDal = new recLogRepo(ConfigurationManager.ConnectionStrings["TrainingConn"].ConnectionString);
    private CabangRepo CabangDal = new CabangRepo(ConfigurationManager.ConnectionStrings["TrainingConn"].ConnectionString);
    private LokasiRepo LokasiDal = new LokasiRepo(ConfigurationManager.ConnectionStrings["TrainingConn"].ConnectionString);
    private KaryawanRepo KaryawanDal = new KaryawanRepo(ConfigurationManager.ConnectionStrings["TrainingConn"].ConnectionString);
    private PositionRepo PositionDal = new PositionRepo(ConfigurationManager.ConnectionStrings["JobDescConn"].ConnectionString);
    
    public override void ProcessRequest(HttpContext context)
    {
        string MethodName = string.Empty;
        base.ProcessRequest(context);

        MethodName = context.Request.Params["method"];


        switch (MethodName.ToLower())
        {
            case ("upload"):
                context.Response.Write(Upload(context));
                break;
            case ("uploadlokasi"):
                context.Response.Write(UploadLokasi(context));
                break;
            case ("uploadkaryawan"):
                context.Response.Write(UploadKaryawan(context));
                break;
            default:
                // do nothing
                break;
        }

        

    }
 
    public bool IsReusable {
        get {
            return false;
        }
    }

    private string Upload(HttpContext context)
    {
        JSONHelper helper = new JSONHelper();

        try
        {

            if (context.Request.Files.Count > 0)
            {
                HttpPostedFile hpf = context.Request.Files[0] as HttpPostedFile;

                var file = context.Request.Files[0];
                string FileName = string.Empty;
                if (HttpContext.Current.Request.Browser.Browser.ToUpper() == "IE")
                {
                    string[] files = hpf.FileName.Split(new char[] { '\\' });
                    FileName = files[files.Length - 1];
                }
                else
                {
                    FileName = hpf.FileName;
                }

                byte[] datas = new byte[hpf.ContentLength];
                hpf.InputStream.Position = 0;
                hpf.InputStream.Read(datas, 0, hpf.ContentLength);

                string path = context.Server.MapPath("~/Upload/DataCabang");
                if (!System.IO.Directory.Exists(path))
                    System.IO.Directory.CreateDirectory(path);

                FileName = System.IO.Path.Combine(path, "DataCabang" + ".xlsx");
                if (System.IO.File.Exists(FileName))
                    System.IO.File.Delete(FileName);

                hpf.SaveAs(FileName);

                hpf.InputStream.Close();
                hpf.InputStream.Dispose();

            }


            FileStream stream = File.Open(context.Server.MapPath(@"~/Upload/DataCabang") + @"/DataCabang" + ".xlsx", FileMode.Open, FileAccess.Read, FileShare.None);

            //1. Reading from a binary Excel file ('97-2003 format; *.xls)
            //IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
            //...
            //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
            IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            //...
            //3. DataSet - The result of each spreadsheet will be created in the result.Tables
            DataSet result = excelReader.AsDataSet();
            //...
            //4. DataSet - Create column names from first row
            excelReader.IsFirstRowAsColumnNames = true;
            DataSet ds = excelReader.AsDataSet();

            //5. Data Reader methods
            List<Cabang> listCabang = new List<Cabang>();
            Cabang obj = new Cabang();

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
               
              
                obj = new Cabang();
                obj.NamaCabang = dr["Cabang"].ToString().Trim();
                obj.CreatedBy = base.UserLoggedIn;
                obj.CreatedDate = DateTime.Now;

                listCabang.Add(obj);

            }
          

            //6. Free resources (IExcelDataReader is IDisposable)
            excelReader.Close();
            CabangDal.SaveUpload(listCabang);
            
            helper.Message = "Data berhasil di Upload !";
            helper.IsSuccess = true;

        }
        catch (Exception ex)
        {

            helper.Message = ex.Message;
            helper.IsSuccess = false;

        }
        finally
        {
            recLog log = new recLog();
            log.LogEvent = "Upload Data Cabang";
            log.LogMessage = helper.Message;
            log.LogSource = "UploadDataHandler.ashx";
            log.CreatedBy = base.UserLoggedIn;
            log.CreatedDate = DateTime.Now;
            logDal.Save(log);

        }

        return Newtonsoft.Json.JsonConvert.SerializeObject(helper);

    }

    private string UploadLokasi(HttpContext context)
    {
        JSONHelper helper = new JSONHelper();

        try
        {

            if (context.Request.Files.Count > 0)
            {
                HttpPostedFile hpf = context.Request.Files[0] as HttpPostedFile;

                var file = context.Request.Files[0];
                string FileName = string.Empty;
                if (HttpContext.Current.Request.Browser.Browser.ToUpper() == "IE")
                {
                    string[] files = hpf.FileName.Split(new char[] { '\\' });
                    FileName = files[files.Length - 1];
                }
                else
                {
                    FileName = hpf.FileName;
                }

                byte[] datas = new byte[hpf.ContentLength];
                hpf.InputStream.Position = 0;
                hpf.InputStream.Read(datas, 0, hpf.ContentLength);

                string path = context.Server.MapPath("~/Upload/DataLokasi");
                if (!System.IO.Directory.Exists(path))
                    System.IO.Directory.CreateDirectory(path);

                FileName = System.IO.Path.Combine(path, "DataLokasi" + ".xlsx");
                if (System.IO.File.Exists(FileName))
                    System.IO.File.Delete(FileName);

                hpf.SaveAs(FileName);

                hpf.InputStream.Close();
                hpf.InputStream.Dispose();

            }


            FileStream stream = File.Open(context.Server.MapPath(@"~/Upload/DataLokasi") + @"/DataLokasi" + ".xlsx", FileMode.Open, FileAccess.Read, FileShare.None);

            //1. Reading from a binary Excel file ('97-2003 format; *.xls)
            //IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
            //...
            //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
            IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            //...
            //3. DataSet - The result of each spreadsheet will be created in the result.Tables
            DataSet result = excelReader.AsDataSet();
            //...
            //4. DataSet - Create column names from first row
            excelReader.IsFirstRowAsColumnNames = true;
            DataSet ds = excelReader.AsDataSet();

            //5. Data Reader methods
            List<Lokasi> listLokasi = new List<Lokasi>();
            Lokasi obj = new Lokasi();

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                
                obj = new Lokasi();
                obj.NamaLokasi = dr["Lokasi"].ToString().Trim();
                obj.CreatedBy = base.UserLoggedIn;
                obj.CreatedDate = DateTime.Now;

                List<Cabang> listCabang = CabangDal.GetList();
                var queryCabang = listCabang.Where(x => x.NamaCabang.Contains(dr["Cabang"].ToString().Trim()));
                foreach (Cabang dataCabang in queryCabang.ToList())
                {
                    obj.CabangID = dataCabang.CabangID;
                }
                
                listLokasi.Add(obj);

            }


            //6. Free resources (IExcelDataReader is IDisposable)
            excelReader.Close();
            LokasiDal.SaveUpload(listLokasi);

            helper.Message = "Data berhasil di Upload !";
            helper.IsSuccess = true;

        }
        catch (Exception ex)
        {

            helper.Message = ex.Message;
            helper.IsSuccess = false;

        }
        finally
        {
            recLog log = new recLog();
            log.LogEvent = "Upload Data Cabang";
            log.LogMessage = helper.Message;
            log.LogSource = "UploadDataHandler.ashx";
            log.CreatedBy = base.UserLoggedIn;
            log.CreatedDate = DateTime.Now;
            logDal.Save(log);

        }

        return Newtonsoft.Json.JsonConvert.SerializeObject(helper);

    }

    private string UploadKaryawan(HttpContext context)
    {
        JSONHelper helper = new JSONHelper();

        try
        {

            if (context.Request.Files.Count > 0)
            {
                HttpPostedFile hpf = context.Request.Files[0] as HttpPostedFile;

                var file = context.Request.Files[0];
                string FileName = string.Empty;
                if (HttpContext.Current.Request.Browser.Browser.ToUpper() == "IE")
                {
                    string[] files = hpf.FileName.Split(new char[] { '\\' });
                    FileName = files[files.Length - 1];
                }
                else
                {
                    FileName = hpf.FileName;
                }

                byte[] datas = new byte[hpf.ContentLength];
                hpf.InputStream.Position = 0;
                hpf.InputStream.Read(datas, 0, hpf.ContentLength);

                string path = context.Server.MapPath("~/Upload/DataKaryawan");
                if (!System.IO.Directory.Exists(path))
                    System.IO.Directory.CreateDirectory(path);

                FileName = System.IO.Path.Combine(path, "DataKaryawan" + ".xlsx");
                if (System.IO.File.Exists(FileName))
                    System.IO.File.Delete(FileName);

                hpf.SaveAs(FileName);

                hpf.InputStream.Close();
                hpf.InputStream.Dispose();

            }


            FileStream stream = File.Open(context.Server.MapPath(@"~/Upload/DataKaryawan") + @"/DataKaryawan" + ".xlsx", FileMode.Open, FileAccess.Read, FileShare.None);

            //1. Reading from a binary Excel file ('97-2003 format; *.xls)
            //IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
            //...
            //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
            IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            //...
            //3. DataSet - The result of each spreadsheet will be created in the result.Tables
            DataSet result = excelReader.AsDataSet();
            //...
            //4. DataSet - Create column names from first row
            excelReader.IsFirstRowAsColumnNames = true;
            DataSet ds = excelReader.AsDataSet();

            //5. Data Reader methods
            List<Lokasi> listLokasi = new List<Lokasi>();
            List<Cabang> listCabang = new List<Cabang>();
            List<Karyawan> listKaryawan = new List<Karyawan>();
            List<Position> listJabatan = new List<Position>();
            Karyawan obj = new Karyawan();
            Lokasi objLokasi = new Lokasi();
            Cabang objCabang = new Cabang();
            Position objJabatan = new Position();

            foreach (DataRow dr in ds.Tables[0].Rows)
            {

                obj = new Karyawan();
                
                obj.NamaKaryawan = dr["Nama Karyawan"].ToString().Trim();
                obj.NIK = dr["NIK"].ToString().Trim();
                
                listCabang = CabangDal.GetList();
                var queryCabang = listCabang.Where(x=>x.NamaCabang.Contains(dr["Cabang"].ToString().Trim()));
                foreach(Cabang data in queryCabang.ToList())
                {
                    obj.CabangID = data.CabangID;  
                }

                listLokasi = LokasiDal.GetList();
                var queryLokasi = listLokasi.Where(x => x.NamaLokasi.Contains(dr["Lokasi"].ToString().Trim()));
                foreach (Lokasi data in queryLokasi.ToList())
                {
                    obj.LokasiID = data.LokasiID;
                }

                listJabatan = PositionDal.GetList();
                var queryJabatan = listJabatan.Where(x => x.PositionName.Contains(dr["Nama Jabatan"].ToString().Trim()));
                foreach (Position data in queryJabatan.ToList())
                {
                    obj.PositionID = data.PositionID;
                }
            
                obj.CreatedBy = base.UserLoggedIn;
                obj.CreatedDate = DateTime.Now;

                listKaryawan.Add(obj);

            }


            //6. Free resources (IExcelDataReader is IDisposable)
            excelReader.Close();
            KaryawanDal.SaveUpload(listKaryawan);

            helper.Message = "Data berhasil di Upload !";
            helper.IsSuccess = true;

        }
        catch (Exception ex)
        {

            helper.Message = ex.Message;
            helper.IsSuccess = false;

        }
        finally
        {
            recLog log = new recLog();
            log.LogEvent = "Upload Data Karyawan";
            log.LogMessage = helper.Message;
            log.LogSource = "UploadDataHandler.ashx";
            log.CreatedBy = base.UserLoggedIn;
            log.CreatedDate = DateTime.Now;
            logDal.Save(log);

        }

        return Newtonsoft.Json.JsonConvert.SerializeObject(helper);

    }
}